﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Linq;

namespace DIProject.Data
{
    public class Party
    {

        /// <summary>
        /// находим тот приходный документ от поставщика из иерархии, из которого пришел товар
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Int32 GetParentId(Int32 id)
        {
            string QueryString = @"with data(id,pid)
as
(
select id, parentid from CA.Documents as d1 where id = @id--  200002539
union all
select d2.id, d2.parentid from CA.Documents as d2 inner join data on d2.id = data.PId
)
select top 1 id from data where pid is null";
            string conStr = Properties.Settings.Default.DIAptekaConnectionString;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                try
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter pId = new SqlParameter("@id", SqlDbType.Int);
                    pId.Value = id;
                    command.Parameters.Add(pId);
                    connection.Open();
                    object obj = command.ExecuteScalar();
                    if (Convert.IsDBNull(obj) || obj == null)
                        return 0;
                    return (Int32)obj;
                }
                catch (Exception ee)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить номер накладной\n" + ee.Message);
                    return 0;
                }
            }
        }
        public static Int32 GetPartyCount(Int32 partyId)
        {
            DataSets.UtilTableAdapters.UtilQueries adapter = new DIProject.DataSets.UtilTableAdapters.UtilQueries();
            object o = adapter.GetPartyCount(partyId);
            if (!Convert.IsDBNull(o))
                return Convert.ToInt32(o);
            return 0;
        }

        /// <summary>
        /// номер документа партии по ее Id
        /// </summary>
        /// <param name="partyId"></param>
        /// <returns></returns>
        public static Int32 GetPartyDocId(Int32 partyId)
        {
            string QueryString = @"SELECT DocId FROM CD.Parties WHERE Id = @Id";
            string conString = Properties.Settings.Default.DIAptekaConnectionString;
            using (SqlConnection connection = new SqlConnection(conString))
            {
                try
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    command.Parameters.AddWithValue("@Id", partyId);
                    connection.Open();
                    object obj = command.ExecuteScalar();
                    if (Convert.IsDBNull(obj) || obj == null)
                        return -1;
                    return Convert.ToInt32(obj);
                }
                catch (SqlException se)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить Id документа партии\n" + se.ToString());
                    return -1;
                }
                catch (Exception ee)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить Id документа партии\n" + ee.ToString());
                    return -1;
                }
            }
        }
        public static Int32 GetDepartmentIdFromDocument(Int32 docId)
        {
            string QueryString = @"select DepartmentId 
from CA.Documents
where Id = @Id";
            string conString = Properties.Settings.Default.DIAptekaConnectionString;
            using (SqlConnection connection = new SqlConnection(conString))
            {
                try
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter PdocId = new SqlParameter("@Id", SqlDbType.Int);
                    PdocId.Value = docId;
                    command.Parameters.Add(PdocId);
                    connection.Open();
                    object obj = command.ExecuteScalar();
                    if (Convert.IsDBNull(obj) || obj == null)
                        return -1;
                    return Convert.ToInt32(obj);
                }
                catch (SqlException se)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить департамент подразделения\n" + se.ToString());
                    return -1;
                }
                catch (Exception ee)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить департамент подразделения\n" + ee.ToString());
                    return -1;
                }
            }
        }
        /// <summary>
        /// контрагент по номеру накладной
        /// </summary>
        /// <param name="docId"></param>
        /// <returns></returns>
        public static Int32 GetContragentIdFromDocument(Int32 docId)
        {
            string QueryString = @"SELECT     ContragentId
FROM         CA.Documents
WHERE     (Id = @id)";
            string conStr = Properties.Settings.Default.DIAptekaConnectionString;
            using (SqlConnection connection = new SqlConnection(conStr))
            {
                try
                {
                    SqlCommand command = new SqlCommand(QueryString, connection);
                    SqlParameter pId = new SqlParameter("@id", SqlDbType.Int);
                    pId.Value = docId;
                    command.Parameters.Add(pId);
                    connection.Open();
                    object obj = command.ExecuteScalar();
                    if (Convert.IsDBNull(obj) || obj == null)
                        return -1;
                    return (Int32)obj;
                }
                catch (Exception ee)
                {
                    System.Windows.Forms.MessageBox.Show("Нельзя получить номер накладной\n" + ee.Message);
                    return -1;
                }
            }
        }
        public static void PopulatePartyTable(DIProject.DataSets.Util.CurrentPartyDataTable table, Int32 deptId, Int32 minusDay)
        {
            table.Rows.Clear();
            table.AcceptChanges();
            string Query = @"DECLARE @tab TABLE (PartyId int, Id int IDENTITY (1, 1), UNIQUE CLUSTERED (PartyId, Id)) 
IF (@DeptId = - 100) BEGIN INSERT INTO @tab (PartyId)
SELECT        CA.GoodsDocLines.PartyId
FROM    CA.GoodsDocLines INNER JOIN CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE        CA.Documents.DocDate < 
DATEADD(d,@MinusDay, CONVERT(DATETIME, CONVERT(VARCHAR(12), GETDATE(), 102), 102))
GROUP BY CA.GoodsDocLines.PartyId
HAVING        (SUM(CA.GoodsDocLines.Qntty) > 0)
INSERT   INTO    @tab (PartyId)
SELECT   Id
FROM   CD.Parties
WHERE  RestQntty > 0 END ELSE
BEGIN
INSERT INTO @tab(PartyId)
SELECT        CA.GoodsDocLines.PartyId
FROM  CA.GoodsDocLines
INNER JOIN CA.Documents ON CA.GoodsDocLines.DocId = CA.Documents.Id
WHERE  
CA.Documents.DocDate<DATEADD(d,@MinusDay,CONVERT(DATETIME,CONVERT(VARCHAR(12),GETDATE(),102),102))
AND CA.Documents.DepartmentId = @DeptId
GROUP BY CA.GoodsDocLines.PartyId
HAVING   (SUM(CA.GoodsDocLines.Qntty)>0)
INSERT INTO @tab(PartyId)
SELECT  Id
FROM   CD.Parties
WHERE  DepartmentId=@DeptId AND RestQntty>0
END
DECLARE @tab1 TABLE (Id int PRIMARY KEY CLUSTERED)
INSERT INTO @tab1(Id)
SELECT DISTINCT   PartyId
FROM  @tab

SELECT vwGoodsDetailed.FullName, CD.Parties.RetailPrice, CD.Parties.RestQntty,
CD.Parties.Blocked, CD.Parties.Id, CD.InputLines.BatchNumber, CD.Parties.BarCode,
CD.InputLines.DocQntty, CA.Contragents.Name AS cName, CD.InputLines.ContragentDocDate,
CD.InputLines.SupplierPrice, vwGoodsDetailed.ManufacturerName, CD.InputLines.ReadyBefore,
CD.Parties.GoodsId as GoodsId
FROM  CA.Contragents INNER JOIN CD.InputLines
ON CA.Contragents.Id = CD.InputLines.ContragentId INNER JOIN
CD.Parties ON CD.InputLines.Id = CD.Parties.InputLineId INNER JOIN vwGoodsDetailed
ON CD.Parties.GoodsId = vwGoodsDetailed.Id INNER JOIN @tab1 AS t
ON t.Id = CD.Parties.Id  ORDER BY 1";
            string connStr = Properties.Settings.Default.DIAptekaConnectionString;
            using (SqlConnection connection = new SqlConnection(connStr))
            {
                SqlCommand command = new SqlCommand(Query, connection);
                command.Parameters.AddWithValue("@DeptId", deptId);
                command.Parameters.AddWithValue("@MinusDay", minusDay);
                connection.Open();
                using (SqlDataReader rd = command.ExecuteReader())
                {
                    while (rd.Read())
                    {
                        DataSets.Util.CurrentPartyRow r = table.NewCurrentPartyRow();
                        r.FullName = rd.GetString(0);
                        r.RetailPrice = rd.GetDecimal(1);
                        r.RestQntty = rd.GetInt32(2);
                        r.Blocked = rd.GetBoolean(3);
                        r.Id = rd.GetInt32(4);
                        if (!rd.IsDBNull(5))
                            r.BatchNumber = rd.GetString(5);
                        r.BarCode = rd.GetString(6);
                        r.DocQntty = rd.GetInt32(7);
                        r.cName = rd.GetString(8);
                        r.ContragentDocDate = rd.GetDateTime(9);
                        r.SupplierPrice = rd.GetDecimal(10);
                        r.ManufacturerName = rd.GetString(11);
                        if (!rd.IsDBNull(12))
                            r.ReadyBefore = rd.GetDateTime(12);
                        r.GoodsId = rd.GetInt32(13);
                        table.Rows.Add(r);
                    }
                }
            }
        }
        public static decimal RestQnttyInSuplierPrice()
        {
            return DataLayer.ContextFun(contest => 
            {
                var res = contest.RestQnttyInSuplierPrice();
                var data = res.Sum(s => s.sum_no);
                return data.HasValue ? data.Value : 0;
            });
        }
    }
}
